﻿using System.Dynamic;
using SqlSugar;
using System.Data;
using IES.NET.Application.Entity;
using System.Drawing;

namespace IES.NET.Application.Service.Stkreview;

/// <summary>
/// Oracle自定义查询服务
/// </summary>
[Route("api/oracleQuery")]  // 添加明确的路由前缀
[ApiDescriptionSettings(ApplicationConst.GroupName, Order = 100)]
public class OracleQueryService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly ISqlSugarClient _oracleDb;

    public OracleQueryService(ISqlSugarClient db)
    {
        _db = db;
        // 获取Oracle连接
        if (_db is SqlSugarScope scope)
        {
            _oracleDb = scope.GetConnectionScope("InFlow");
            if (_oracleDb == null)
            {
                throw new Exception("无法获取Oracle数据库连接，请检查配置ID: InFlow");
            }
        }
        else
        {
            throw new Exception($"数据库客户端类型错误，期望SqlSugarScope但得到: {_db.GetType().Name}");
        }
    }

    /// <summary>
    /// 执行查询SQL返回动态对象列表
    /// </summary>
    private async Task<List<ExpandoObject>> QueryDynamicList(string sql, object parameters = null)
    {
        // 使用 SqlQueryAsync<dynamic> 替代 SqlQueryDynamicAsync
        return await _oracleDb.Ado.SqlQueryAsync<ExpandoObject>(sql, parameters);
    }
       

    /// <summary>
    /// 执行查询SQL返回DataTable
    /// </summary>
    private async Task<DataTable> GetDataTable(string sql, object parameters = null)
    {
        return await _oracleDb.Ado.GetDataTableAsync(sql, parameters);
    }
    /// <summary>
    /// 获取数据表
    /// </summary>
    [HttpGet("dataTable")]
    [ApiDescriptionSettings(Name = "GetDataTable")]
    public async Task<ApiResponse<DataTable>> GetDataTableEndpoint([FromQuery] string sql, [FromQuery] object parameters = null)
    {
        try
        {
            var result = await GetDataTable(sql, parameters);
            return new ApiResponse<DataTable>
            {
                Data = result,
                Success = true,
                Message = "数据获取成功"
            };
        }
        catch (Exception ex)
        {
            return new ApiResponse<DataTable>
            {
                Success = false,
                Message = $"查询出错: {ex.Message}"
            };
        }
    }
    /// <summary>
    /// 执行更新SQL语句
    /// </summary>
    private async Task<int> ExecuteCommand(string sql, object parameters = null)
    {
        return await _oracleDb.Ado.ExecuteCommandAsync(sql, parameters);
    }
    /// <summary>
    /// 执行命令
    /// </summary>
    [HttpPost("executeCommand")]
    [ApiDescriptionSettings(Name = "ExecuteCommand")]
    public async Task<ApiResponse<int>> ExecuteCommandEndpoint([FromBody] CommandRequest request)
    {
        try
        {
            var result = await ExecuteCommand(request.Sql, request.Parameters);
            return new ApiResponse<int>
            {
                Data = result,
                Success = true,
                Message = "命令执行成功"
            };
        }
        catch (Exception ex)
        {
            return new ApiResponse<int>
            {
                Success = false,
                Message = $"执行出错: {ex.Message}"
            };
        }
    }
    public class ApiResponse<T>
    {
        public bool Success { get; set; }
        public string Message { get; set; }
        public T Data { get; set; }
    }

    /// <summary>
    /// 查询JOB下最新版本的数据
    /// </summary>
    [HttpGet("getJobStackupInfos")]
    [ApiDescriptionSettings(Name = "GetJobStackupInfos")]
    public async Task<ApiResponse<JobStkInfo>> GetJobStackupInfos([FromQuery] string jobName)
    {
        if (string.IsNullOrEmpty(jobName))
        {
            return new ApiResponse<JobStkInfo>
            {
                Success = false,
                Message = "料号名称不能为空"
            };
        }

        try
        {
            var sql = $@"select r.* from(
                            select rev.revision_id,item_job.item_id
                            from SOMACIS.revisions rev,SOMACIS.items item_graph ,SOMACIS.items item_job
                            where item_job.item_name='{jobName}' 
                            and rev.item_id=item_graph.item_id 
                            and item_graph.root_id=item_job.item_id
                            and item_graph.item_type=1 
                            and item_job.item_type=2 order by rev.revision_id desc
                            ) r 
                            where rownum=1";

            DataTable tmpTable = await _oracleDb.Ado.GetDataTableAsync(sql);
            if (tmpTable.Rows.Count == 0)
            {
                return new ApiResponse<JobStkInfo>
                {
                    Success = false,
                    Message = "InFlow不存在此料号"
                };
            }

            Int32 theRevId = Convert.ToInt32(tmpTable.Rows[0][0]);
            Int32 theItemId = Convert.ToInt32(tmpTable.Rows[0][1]);

            sql = $@"select b.BLOB_DATA STACKUP_IMAGE, jd.STATUS_THICKNESS_,jd.STATUS_IMPEDANCE_,
                    jd.STATUS_RESIN_STARVATION_, jd.CHECK_NOTES_STACKUP_INFO_ , 
                    jd.CHECK_NOTES_STACKUP_WARN_ , jd.CHECK_NOTES_STACKUP_CRITICAL_,
                    jd.ASSIGNED_USER_,jd.revision_id 
                    from somacis.JOB j,somacis.JOB_DA jd,somacis.content c,
                    somacis.items item,somacis.REV_CONTROLLED_LOB b
                    where item.root_id={theItemId}
                    and c.from_graph={theRevId}
                    and j.revision_id=c.revision_id
                    and j.item_id=c.item_id
                    and item.item_id=c.item_id
                    and j.item_id=jd.item_id
                    and j.revision_id=jd.revision_id
                    and b.LOB_ID = jd.STACKUP_IMAGINE_";

            DataTable dtCheckNotes = await _oracleDb.Ado.GetDataTableAsync(sql);
            DataRow row = dtCheckNotes.Rows[0];

            var jobStkInfo = new JobStkInfo
            {
                thickness_status = (int)row["STATUS_THICKNESS_"] == 1 ? "OK" : "NG",
                impedance_status = (int)row["STATUS_IMPEDANCE_"] == 1 ? "OK" : "NG",
                resin_starvation_status = (int)row["STATUS_RESIN_STARVATION_"] == 1 ? "OK" : "NG",
                stackup_check_info = row["CHECK_NOTES_STACKUP_INFO_"].ToString(),
                stackup_check_warn = row["CHECK_NOTES_STACKUP_WARN_"].ToString(),
                stackup_check_critical = row["CHECK_NOTES_STACKUP_CRITICAL_"].ToString(),
                stackup_image = (byte[])row["STACKUP_IMAGE"]
            };
            return new ApiResponse<JobStkInfo>
            {
                Data = jobStkInfo,
                Success = true,
                Message = "数据加载成功"
            };
        }
        catch (Exception ex)
        {
            return new ApiResponse<JobStkInfo>
            {
                Success = false,
                Message = $"查询出错: {ex.Message}"
            };
        }
    }
    /// <summary>
    /// 获取动态列表
    /// </summary>
    [HttpGet("dynamicList")]
    [ApiDescriptionSettings(Name = "GetDynamicList")]
    public async Task<ApiResponse<List<ExpandoObject>>> GetDynamicListEndpoint([FromQuery] string sql, [FromQuery] object parameters = null)
    {
        try
        {
            var result = await QueryDynamicList(sql, parameters);
            return new ApiResponse<List<ExpandoObject>>
            {
                Data = result,
                Success = true,
                Message = "数据获取成功"
            };
        }
        catch (Exception ex)
        {
            return new ApiResponse<List<ExpandoObject>>
            {
                Success = false,
                Message = $"查询出错: {ex.Message}"
            };
        }
    }
    
}
// 添加请求模型类
public class CommandRequest
{
    public string Sql { get; set; }
    public object Parameters { get; set; }
}